<?php

class ThongKe extends Database
{
    public function getNangBacList()
    {
        $sql = <<<SQL
SELECT cb.cb_id AS cb_id,
		cb_hoten,
		n_id,
		b_id,
		DATE_FORMAT(cb_lastupd, '%d/%m/%Y') AS cb_lastupd,
		if(YEAR(NOW()) - YEAR(kl_ngay) <= 3, DATE_FORMAT(kl_ngay, '%d/%m/%Y'), NULL) AS kl_ngay
FROM canbo cb LEFT JOIN kyluat kl ON cb.cb_id = kl.cb_id
WHERE
	(kl_ngay IS NULL AND year(NOW()) - year(cb_lastupd) >= 3)
	OR
		(year(now()) - year(kl_ngay) <= 3 AND year(NOW()) - year(cb_lastupd) >= 4)
SQL;

       return $this->run($sql);

    }

    public function checkNangBac($id)
    {
        $sql = <<<sql
SELECT cb.cb_id,
		cb_hoten,
		n_id,
		b_id,
		DATE_FORMAT(cb_lastupd, '%d/%m/%Y') AS cb_lastupd,
		if(YEAR(NOW()) - YEAR(kl_ngay) <= 3, DATE_FORMAT(kl_ngay, '%d/%m/%Y'), NULL) AS kl_ngay
FROM canbo cb LEFT JOIN kyluat kl ON cb.cb_id = kl.cb_id
WHERE
	cb.cb_id = :id AND
	(
	(kl_ngay IS NULL AND year(NOW()) - year(cb_lastupd) >= 3)
	OR
		(year(now()) - year(kl_ngay) <= 3 AND year(NOW()) - year(cb_lastupd) >= 4)
	)
sql;

        $result = $this->run($sql, array('id' => $id));

        return count($result) > 0;
    }

    public function nangbaccb($id)
    {
        $sql = <<<sql
UPDATE canbo SET b_id = b_id + 1, cb_lastupd = NOW()
WHERE cb_id = :id;
sql;
        return $this->run($sql, array('id' => $id)) == 1;
    }
}
